package com.example.webdemo.dao.Impl;

import com.example.webdemo.bean.orderitem;
import com.example.webdemo.bean.orders;
import com.example.webdemo.dao.OrderDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;


import java.sql.*;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import com.example.webdemo.bean.customer;


@Repository
public class OrderDaoImpl implements OrderDao {

    private final JdbcTemplate jdbcTemplate;
    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    // 字典缓存
    private final Map<String, Map<String, String>> dictCache = new HashMap<>();

    @Autowired
    public OrderDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        loadDictCache();
    }

    // 加载字典数据到缓存
    private void loadDictCache() {
        String sql = "SELECT dict_type, dict_code, dict_name FROM base_dict WHERE status = 1 ORDER BY sort";
        jdbcTemplate.query(sql, (rs, rowNum) -> {
            String dictType = rs.getString("dict_type");
            String dictCode = rs.getString("dict_code");
            String dictName = rs.getString("dict_name");

            dictCache.computeIfAbsent(dictType, k -> new HashMap<>())
                    .put(dictCode, dictName);
            return null;
        });
    }

    // 根据字典类型和代码获取名称
    private String getDictName(String dictType, String dictCode) {
        if (dictCode == null) return null;
        Map<String, String> typeMap = dictCache.get(dictType);
        return typeMap != null ? typeMap.getOrDefault(dictCode, dictCode) : dictCode;
    }

    // 根据字典类型和名称获取代码
    private String getDictCode(String dictType, String dictName) {
        if (dictName == null) return null;
        Map<String, String> typeMap = dictCache.get(dictType);
        if (typeMap != null) {
            for (Map.Entry<String, String> entry : typeMap.entrySet()) {
                if (entry.getValue().equals(dictName)) {
                    return entry.getKey();
                }
            }
        }
        return dictName;
    }

    @Override
    public int createOrder(orders order) {
        String sql = "INSERT INTO orders(order_no, cust_id, order_amount, order_status, " +
                "order_time, pay_time, delivery_time, complete_time, order_remark, user_id) " +
                "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        return jdbcTemplate.update(sql,
                order.getOrder_no(),
                order.getCust_id(),
                order.getOrder_amount(),
                getDictCode("order_status", order.getOrder_status()),
                order.getOrder_time(),
                order.getPay_time(),
                order.getDelivery_time(),
                order.getComplete_time(),
                order.getOrder_remark(),
                order.getUser_id());
    }

    @Override
    public orders getOrderById(int id) {
        String sql = "SELECT * FROM orders WHERE order_id = ?";
        try {
            orders order = jdbcTemplate.queryForObject(sql, new OrderRowMapper(), id);
            if (order != null) {
                // 加载订单项
                order.setItems(getOrderItemsByOrderId(id));
                // 加载客户信息
                order.setCustomers(getCustomerByOrderId(id));
            }
            return order;
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public List<orders> searchOrderList(String orderNo, Integer custId, String status) {
        StringBuilder sql = new StringBuilder("SELECT * FROM orders WHERE 1=1 ");
        Map<String, Object> params = new HashMap<>();

        if (orderNo != null && !orderNo.isEmpty()) {
            sql.append("AND order_no LIKE :orderNo ");
            params.put("orderNo", "%" + orderNo + "%");
        }
        if (custId != null) {
            sql.append("AND cust_id = :custId ");
            params.put("custId", custId);
        }
        if (status != null && !status.isEmpty()) {
            sql.append("AND order_status = :status ");
            params.put("status", getDictCode("order_status", status));
        }
        sql.append("ORDER BY order_time DESC");

        List<orders> orderList = namedParameterJdbcTemplate.query(sql.toString(), params, new OrderRowMapper());

        // 批量加载关联数据
        if (!orderList.isEmpty()) {
            // 获取所有订单ID
            List<Integer> orderIds = orderList.stream()
                    .map(order -> order.getOrder_id())
                    .collect(Collectors.toList());

            // 批量获取订单项
            Map<Integer, List<orderitem>> itemsMap = getOrderItemsByOrderIds(orderIds);

            // 获取所有客户ID
            List<Integer> custIds = orderList.stream()
                    .map(order -> order.getCust_id())
                    .distinct()
                    .collect(Collectors.toList());

            // 批量获取客户信息
            Map<Integer, customer> customersMap = getCustomersByIds(custIds);

            // 设置关联数据
            orderList.forEach(order -> {
                order.setItems(itemsMap.getOrDefault(order.getOrder_id(), Collections.emptyList()));

                customer cust = customersMap.get(order.getCust_id());
                if (cust != null) {
                    order.setCustomers(Collections.singletonList(cust));
                } else {
                    order.setCustomers(Collections.emptyList());
                }
            });
        }

        return orderList;
    }

    @Override
    public List<orders> getAllOrders() {
        // 1. 查询所有订单
        String orderSql = "SELECT * FROM orders ORDER BY order_time ASC ";
        List<orders> orders = jdbcTemplate.query(orderSql, new OrderRowMapper());

        if (orders.isEmpty()) {
            return Collections.emptyList();
        }

        // 2. 批量加载关联数据
        List<Integer> orderIds = orders.stream()
                .map(order -> order.getOrder_id())
                .collect(Collectors.toList());

        // 批量获取订单项
        Map<Integer, List<orderitem>> itemsMap = getOrderItemsByOrderIds(orderIds);

        // 获取所有客户ID
        List<Integer> custIds = orders.stream()
                .map(order -> order.getCust_id())
                .distinct()
                .collect(Collectors.toList());

        // 批量获取客户信息
        Map<Integer, customer> customersMap = getCustomersByIds(custIds);

        // 3. 设置关联数据
        orders.forEach(order -> {
            order.setItems(itemsMap.getOrDefault(order.getOrder_id(), Collections.emptyList()));

            customer cust = customersMap.get(order.getCust_id());
            if (cust != null) {
                order.setCustomers(Collections.singletonList(cust));
            } else {
                order.setCustomers(Collections.emptyList());
            }
        });

        return orders;
    }

    @Override
    public int updateOrderStatus(int id, String status) {
        String sql = "UPDATE orders SET order_status = ? WHERE order_id = ?";
        return jdbcTemplate.update(sql, getDictCode("order_status", status), id);
    }

    @Override
    public int deleteOrder(int id) {
        // 先删除关联的订单项
        String deleteItemsSql = "DELETE FROM orderitem WHERE order_id = ?";
        jdbcTemplate.update(deleteItemsSql, id);

        // 再删除订单
        String deleteOrderSql = "DELETE FROM orders WHERE order_id = ?";
        return jdbcTemplate.update(deleteOrderSql, id);
    }

    @Override
    public Map<String, Object> getOrderAmountStats(String startDate, String endDate) {
        StringBuilder sql = new StringBuilder(
                "SELECT COUNT(*) as total, SUM(order_amount) as amount, " +
                        "AVG(order_amount) as avg_amount FROM orders WHERE 1=1 ");

        Map<String, Object> params = new HashMap<>();

        if (startDate != null && !startDate.isEmpty()) {
            sql.append("AND order_time >= :startDate ");
            params.put("startDate", startDate);
        }
        if (endDate != null && !endDate.isEmpty()) {
            sql.append("AND order_time <= :endDate ");
            params.put("endDate", endDate);
        }

        return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
    }

    // 获取单个订单的订单项
    private List<orderitem> getOrderItemsByOrderId(int orderId) {
        String sql = "SELECT * FROM orderitem WHERE order_id = ?";
        return jdbcTemplate.query(sql, new OrderItemRowMapper(), orderId);
    }

    // 批量获取多个订单的订单项
    private Map<Integer, List<orderitem>> getOrderItemsByOrderIds(List<Integer> orderIds) {
        if (orderIds.isEmpty()) {
            return Collections.emptyMap();
        }

        String sql = "SELECT * FROM orderitem WHERE order_id IN (" +
                orderIds.stream().map(String::valueOf)
                        .collect(Collectors.joining(",")) + ")";

        List<orderitem> items = jdbcTemplate.query(sql, new OrderItemRowMapper());

        return items.stream()
                .collect(Collectors.groupingBy(orderitem::getOrder_id));
    }

    // 获取订单关联的客户信息
    private List<customer> getCustomerByOrderId(int orderId) {
        String sql = "SELECT c.* FROM customer c JOIN orders o ON c.cust_id = o.cust_id WHERE o.order_id = ?";
        return jdbcTemplate.query(sql, new CustomerRowMapper(), orderId);
    }

    // 批量获取客户信息
    private Map<Integer, customer> getCustomersByIds(List<Integer> custIds) {
        if (custIds.isEmpty()) {
            return Collections.emptyMap();
        }

        String sql = "SELECT * FROM customer WHERE cust_id IN (" +
                custIds.stream().map(String::valueOf)
                        .collect(Collectors.joining(",")) + ")";

        List<customer> customers = jdbcTemplate.query(sql, new CustomerRowMapper());

        return customers.stream()
                .collect(Collectors.toMap(customer::getCust_id, cust -> cust));
    }

    // 获取订单状态选项
    public Map<String, String> getOrderStatusOptions() {
        return dictCache.getOrDefault("order_status", new HashMap<>());
    }

    // 订单行映射器
    private class OrderRowMapper implements RowMapper<orders> {
        @Override
        public orders mapRow(ResultSet rs, int rowNum) throws SQLException {
            orders order = new orders();
            order.setOrder_id(rs.getInt("order_id"));
            order.setOrder_no(rs.getString("order_no"));
            order.setCust_id(rs.getInt("cust_id"));
            order.setOrder_amount(rs.getDouble("order_amount"));

            // 转换订单状态为显示名称
            String statusCode = rs.getString("order_status");
            order.setOrder_status(getDictName("order_status", statusCode));

            order.setOrder_time(rs.getTimestamp("order_time"));
            order.setPay_time(rs.getTimestamp("pay_time"));
            order.setDelivery_time(rs.getTimestamp("delivery_time"));
            order.setComplete_time(rs.getTimestamp("complete_time"));
            order.setOrder_remark(rs.getString("order_remark"));
            order.setUser_id(rs.getInt("user_id"));
            order.setCreate_time(rs.getTimestamp("create_time"));
            order.setUpdate_time(rs.getTimestamp("update_time"));

            // items和customers将在外部设置
            return order;
        }
    }

    // 订单项行映射器
    private static class OrderItemRowMapper implements RowMapper<orderitem> {
        @Override
        public orderitem mapRow(ResultSet rs, int rowNum) throws SQLException {
            orderitem item = new orderitem();
            item.setItem_id(rs.getInt("item_id"));
            item.setOrder_id(rs.getInt("order_id"));
            item.setProd_id(rs.getInt("prod_id"));
            item.setQuantity(rs.getInt("quantity"));
            item.setUnit_price(rs.getDouble("unit_price"));
            item.setItem_amount(rs.getDouble("item_amount"));
            item.setItem_remark(rs.getString("item_remark"));
            return item;
        }
    }

    // 客户行映射器
    private static class CustomerRowMapper implements RowMapper<customer> {
        @Override
        public customer mapRow(ResultSet rs, int rowNum) throws SQLException {
            customer customer = new customer();
            customer.setCust_id(rs.getInt("cust_id"));
            customer.setCust_name(rs.getString("cust_name"));
            customer.setCust_type(rs.getString("cust_type"));
            customer.setCust_source(rs.getString("cust_source"));
            customer.setCust_industry(rs.getString("cust_industry"));
            customer.setCust_level(rs.getString("cust_level"));
            customer.setCust_linkman(rs.getString("cust_linkman"));
            customer.setCust_phone(rs.getString("cust_phone"));
            customer.setCust_mobile(rs.getString("cust_mobile"));
            customer.setCust_email(rs.getString("cust_email"));
            customer.setCust_address(rs.getString("cust_address"));
            customer.setCust_remark(rs.getString("cust_remark"));
            customer.setUser_id(rs.getInt("user_id"));
            customer.setCreate_time(rs.getTimestamp("create_time"));
            customer.setUpdate_time(rs.getTimestamp("update_time"));
            return customer;
        }
    }
}